<?php
/**
 * 
 *  @author: Rick van Schalkwijk
 * 	@date:  2012
 *  @version: 0.1.2
 *
 *	Changes:
 *  0.1.1
 *  - Rick S. Added ::result_field()
 *  0.1.0
 *  0.0.8
 *  - Rick S. Added mysql_set_charset( 'utf8' ) to ::connect()
 *  0.0.7
 *  - Niek S. Added ::escape()
 *  0.0.6
 *  - Rick S. made new read_all(), returns complete table
 *  - Rick S. renamed read_all() to read_all_where()
 *  0.0.5
 *	- Rick S. DBH::create() now returns insert id or false
 *  0.0.4
 *  - Rick S. replaced Exeption with log::fatal()
 *  0.0.3
 *  - Rick S. Not more backwards compatible. Changed some arguments
 *	0.0.2
 *	- Changed update() to 3 params instead of 4
 *	- Changed insert() to 2 params instead of 3
 *	0.0.1
 *	- Class created and added the first functions, getInstance/connect/query/insert/update/fetch_row/remove_row
 *
 * 	Description
 *
 *  Database class, using CRUD names
 */

class DBH {

	// object instance
	private static $instance;

	private $connection;

	private function __construct() {}
	private function __clone() {}

	/**
	 * 	Close database connection
	 */
	public function __destruct() {

		@mysql_close($this->connection);
	}

	/**
	 *
	 */
	public static function getInstance() {
		 
		if (!DBH::$instance instanceof self) {
			 
			DBH::$instance = new self();

			DBH::$instance->connect();
		}

		return DBH::$instance;
	}

	/**
	 * 	Make database connection
	 */
	private function connect() {

		$config = Settings::get('db');

		$this->connection = @mysql_connect($config['host'], $config['user'], $config['password']);
		if(!$this->connection) {
				
			log::fatal("No database connection" ,$this, 'connect()');
		}

		// Set mysql charset to utf8
		mysql_set_charset( 'utf8' );

		if(!mysql_select_db($config['database'], $this->connection)) {
				
			log::fatal("No database selected `{$config['database']}`", $this, 'connect()');
		}
	}

	/**
	 * Executes an mysql query
	 * @param string sql
	 * @return mysql result
	 */
	public function query( $sql ) {

		if( !$result = mysql_query( $sql ) ) {
				
			log::write( mysql_error() . ', ' . $sql, $this, 'query()', 'query' );
			return false;
		}

		return $result;
	}

	/**
	 * Create
	 *
	 * @param object $data
	 * @param string [$table] optional
	 * @return int inserted row id
	 * if false then ERORRe
	 */
	public function create ( $object, $table ) {

		if( is_object( $object ) == false && is_array( $object ) == false ) {
				
			log::write( 'Expects and object', $this, 'create()', 'query' );
			return;
		}

		$sql = "INSERT INTO `{$table}` (";
		$vsql = ") VALUES(";

		foreach($object as $col => $value) {
				
			$sql .= "`{$col}`, ";
			$vsql .= "'" . $this->escape($value) . "', ";
		}

		$sql = rtrim($sql, ', ');
		$sql .= rtrim($vsql, ', ');
		$sql .= ');';

		return $this->query($sql) ? mysql_insert_id() : false;
	}


	/**
	 * Fetches an row
	 *
	 * @param string $sql sql where clue
	 * @param string $table_name tabel naam
	 * @return object
	 */
	public function read( $sql, $table ) {

		if( $sql == false || $table == false ){

			log::write( 'Expects Query', $this, 'read()', 'query' );
			return;
		}

		$result = $this->query("SELECT * FROM `{$table}` WHERE " . $sql);
		return @mysql_fetch_object($result);
	}

	/**
	 * pull a table
	 * @param string $table
	 * @return string
	 */
	public function read_all( $table ){

		if($table == false){

			log::write('Expects table', $this, 'read-all()', 'query');
			return;
		}

		$result = $this->query("SELECT * FROM `$table`");

		if( !$result ) {
			 
			return false;
		}

		$all_result = array();

		while( $row = mysql_fetch_object($result) ){

			$all_result[] = $row;

		}
		return $all_result;
	}

	/*
	 * pull a table with sort param and order
	 * @param string $table
	 * @param string $sortClause
	 * @param string $order
	 * @return string
	 */
	public function read_all_sort( $table, $sortClause, $order ){

		if($table == false){

			log::write('Expects table', $this, 'read-all()', 'query');
			return;
		}

		$result = $this->query("SELECT * FROM `$table` ORDER BY `$sortClause` $order");

		if( !$result ) {

			return false;
		}

		$all_result = array();

		while( $row = mysql_fetch_object($result) ){

			$all_result[] = $row;

		}
		return $all_result;
	}

	/*
	 * read with limit
	 * @param int start, limit
	 * @param string table
	 * return string
	 */
	public function read_limit( $start, $limit, $table){

		if($table == false){

			log::write('Expects table', $this, 'read_limit', 'query');
			return;
		}

		$result = $this->query( "SELECT * FROM `$table` LIMIT $start, $limit");

		if( !$result ){
			return false;
		}

		$all_result = array();

		while( $row = mysql_fetch_object( $result ) ){
			$all_result[] = $row;
		}

		return $all_result;
	}
	 

	/**
	 * Read multiple rows
	 * @param object $name $where clause
	 * @param string $table table name
	 * @return string
	 */
	public function read_all_where( $object, $table ){

		if( is_object( $object ) == false && is_array( $object ) == false ){

			log::write( 'Expects object', $this, 'readall()', 'query' );
			return;
		}

		$where = '';

		foreach( $object as $key => $value ){

			$where .= "`$key`= '" . mysql_real_escape_string($value) . "' AND";
		}

		$where = substr( $where, 0, strlen($where) - 4 );

		$result = $this->query( "SELECT * FROM `{$table}` WHERE " . $where );

		$all_result = array();

		while( $row = mysql_fetch_object($result) ){

			$all_result[] = $row;
		}

		return $all_result;
	}


	/**
	 * 	Update
	 *
	 * @param object $data
	 * @param string $where sql where clause
	 * @param string $table table name
	 * @return mysql_result
	 */
	public function update ( $object, $where, $table ) {

		if( is_object( $object ) == false && is_array( $object ) == false ) {
				
			log::write( 'Expects and object', $this, 'update()', 'query' );
			return;
		}

		$sql = "UPDATE `{$table}` SET ";

		foreach( $object as $col => $value ) {
				
			if( $value === NULL ) {

				$sql .= "`{$col}`=NULL, ";
			} else {

				$sql .= "`{$col}`='" . mysql_real_escape_string($value) . "', ";
			}
		}

		$sql = rtrim($sql, ', ');
		$sql .= " WHERE " . $where;

		return $this->query($sql);
	}

	/**
	 *	Delete a row or multible rows from table
	 * @param string $sql where clause
	 * @param string $table table name
	 * @return bool
	 */
	public function delete( $sql, $table ) {

		return $this->query("DELETE FROM `{$table}` WHERE " . $sql);
	}

	/**
	 *	Escapes a string using mysql_real_escape_string()
	 *
	 * @param string $table string
	 * @return string unescaped string
	 */
	public function escape( $string ) {

		return mysql_real_escape_string( $string );
	}

	/**
	 * Returns a single field from an mysql result
	 *
	 * @param object $result mysql result object
	 * @param string $field field name in table
	 * @return array
	 */
	public function result_field ( $results, $field ) {

		$data = array();

		foreach( $results as &$result ) {
			$data[] = $result->$field;
		}

		return $data;
	}

	/***
	 *Returns a a result count
	 *@param
	 *
	 */
	public function countWhere($table, $alias, $whereClause ){

		if($table == false){
			log::write('Expects table', $this, 'read-all()', 'query');
			return;
		}

		$result = $this->query("SELECT COUNT(*) as '$alias' FROM `$table` WHERE " . $whereClause);

		if( !$result ) {

			return false;
		}
		return $result;
	}

	/***
	 *Returns a a result count
	 *@param
	 *
	 */
	public function count($table){

		if($table == false){
			log::write('Expects table', $this, 'read-all()', 'query');
			return;
		}

		$result = $this->query("SELECT COUNT(*) FROM `$table`");

		if( !$result ) {
			return false;
		}
		return mysql_fetch_object($result);
	}


}
